/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import conexao.Conexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import modelo.Cliente;

public class ClienteDAO {

    private Connection conexao;
    private PreparedStatement stmt;
    private ResultSet rs;
    private String sql;

    public ClienteDAO() throws Exception {
        try {
            this.conexao = Conexao.getConexao();
        } catch (Exception ex) {
            throw new Exception("Erro: " + ex.getMessage());
        }
    }

    public void finalizar() throws Exception {
        try {
            Conexao.closeConexao(this.conexao, this.stmt, this.rs);
        } catch (SQLException ex) {
            throw new Exception("Erro ao finalizar " + ex);
        }
    }
    
    public void incluir(Cliente cliente) throws Exception {
        try {
            sql = "insert into CLIENTE (NOME, NOMEFANTASIA, ENDERECO, TIPOPESSOA, " + 
                  "TELEFONE, EMAIL) values (?, ?, ?, ?, ?, ?)";
            stmt = conexao.prepareStatement(sql);
            //stmt.setInt(1, cliente.getId());
            stmt.setString(1, cliente.getNome());   
            stmt.setString(2, cliente.getFantasia());                    
            stmt.setString(3, cliente.getEndereco());
            stmt.setString(4, cliente.getTipoPessoa());
            stmt.setString(5, cliente.getFone());
            stmt.setString(6, cliente.getEmail());
            
            stmt.execute();
            stmt.close();
        } catch (SQLException ex) {
            throw new Exception("Erro ao incluir " + ex);
        }
    }

    public void alterar(Cliente cliente) throws Exception {
        try {
            sql = "update CLIENTE set NOME=? where ID=?";
            stmt = conexao.prepareStatement(sql);
            stmt.setString(1, cliente.getNome());
            stmt.setInt(2, cliente.getId());
            stmt.execute();
            stmt.close();
        } catch (SQLException ex) {
            throw new Exception("Erro ao alterar " + ex);
        }
    }

    public void excluir(Integer id) throws Exception {
        try {
            sql = "delete from CLIENTE where ID=?";
            stmt = conexao.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.execute();
            stmt.close();
        } catch (SQLException ex) {
            throw new Exception("Erro ao excluir " + ex);
        }
    }

    public Cliente consultar(Integer id) throws Exception {
        try {
            sql = "select * from CLIENTE where ID=?";
            stmt = conexao.prepareStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();           
            Cliente cliente = new Cliente();
            
            if (rs.next()) {
                cliente.setId(rs.getInt("id"));
                cliente.setNome(rs.getString("nome"));
                cliente.setFantasia(rs.getString("nomefantasia"));
                cliente.setEndereco(rs.getString("endereco"));
                cliente.setTipoPessoa(rs.getString("tipopessoa"));
                cliente.setFone(rs.getString("telefone"));
                cliente.setEmail(rs.getString("email"));
            }
            rs.close();
            stmt.close();
            
            return cliente;
        } catch (SQLException ex) {
            throw new Exception("Erro ao consultar " + ex);
        }    
    }    
    
    public List<Cliente> listar() throws Exception {
        try {
            sql = "select * from CLIENTE order by NOME";
            stmt = conexao.prepareStatement(sql);
            rs = stmt.executeQuery();
            List<Cliente> clientes = new ArrayList<Cliente>();
            
            while (rs.next()) {
                Cliente cliente = new Cliente();
                cliente.setId(rs.getInt("id"));
                cliente.setNome(rs.getString("nome"));
                cliente.setFantasia(rs.getString("nomefantasia"));                        
                cliente.setEndereco(rs.getString("endereco"));
                cliente.setTipoPessoa(rs.getString("tipopessoa"));
                cliente.setFone(rs.getString("telefone"));
                cliente.setEmail(rs.getString("email"));
                
                clientes.add(cliente);
            }
            rs.close();
            stmt.close();
            return clientes;
        } catch (SQLException ex) {
            throw new Exception("Erro ao listar " + ex);
        }
    }    
    
}
